/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sistemperpustakaan.model;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.swing.JOptionPane;
import sistemperpustakaan.entity.TransaksiView;
import sistemperpustakaan.entity.TransaksiDetailView;
import sistemperpustakaan.entity.ViewTransaksi;
import sistemperpustakaan.utility.DateUtility;
import sistemperpustakaan.utility.DbUtility;

/**
 *
 * @author EKo
 */
public class TransaksiModel {

    Connection connection;
    ViewTransaksi vt;
    List<ViewTransaksi> listTransaksi;
    TransaksiView tv;
    TransaksiDetailView detailTransaksi;
    List<TransaksiDetailView> ListDetailTransaksi;
    DateUtility du = new DateUtility();

    public TransaksiModel() {
        connection = new DbUtility().getConnection();
    }

    public List<ViewTransaksi> getListTransaksi(String id, String tglawal, String tglakhir, String nis, String nama, String judul, int opt) {
        Date date = null;
        listTransaksi = new ArrayList<ViewTransaksi>();
        String query = "CALL pCariTransaksi ('" + id + "','" + tglawal + "','" + tglakhir + "','" + nis + "','" + nama + "','" + judul + "'," + opt + ")";
//        System.out.println(query);
        CallableStatement statement = null;
        try {
            statement = connection.prepareCall(query);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                vt = new ViewTransaksi(
                        rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getString(5),
                        rs.getInt(6), rs.getString(7), rs.getString(8),
                        rs.getDate(9), rs.getDate(10), rs.getString(11), rs.getString(12), rs.getInt(13), rs.getInt(14));
                listTransaksi.add(vt);
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data \n Error: " + e.getMessage());
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return listTransaksi;
    }


    public TransaksiDetailView generateIdTransaksiDetail() {
        PreparedStatement statement = null;
        TransaksiView transaksi = null;
        try {
            statement = connection.prepareStatement("SELECT dbo.fTransaksiDetail_GenerateIdTransaksiDetail() AS Kode");
//            statement.setInt(1, idKategori);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
//                anggota = new Anggota(rs.getInt(1), "","","","","","","","");
                detailTransaksi = new TransaksiDetailView(rs.getInt(1), 0, null, null, null, null, 0, 0);
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data" + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return detailTransaksi;
    }

    public int countPeminjamanNotCompleteByAnggota(int anggota_id) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("SELECT COUNT(*) FROM vTransaksi  WHERE status='Belum Selesai' AND anggota_id =" + anggota_id + "");
            //statement.setString(1, transaksi_id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception e) {
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return 0;
    }

    public int countInventarisPeminjaman(String inventaris_id) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("SELECT COUNT(*) from vTransaksi WHERE status='Belum Selesai' AND inventaris_id ='" + inventaris_id + "'");
            //statement.setString(1, transaksi_id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception e) {
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return 0;
    }

    public int cekDataInventaris(String inventaris_id) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("select COUNT(*) from vInventaris where status='Tersedia' AND id_inventaris='" + inventaris_id + "'");
            //statement.setString(1, transaksi_id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception e) {
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return 0;
    }

    public int cekStatusAnggota(int anggota_id) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("select tipe_anggota_id from anggota where id =" + anggota_id + "");
            //statement.setString(1, transaksi_id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception e) {
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return 0;
    }

    /*
     * Methode untuk menginsert data peminjaman baru ke table pinjam,
     * table detail pinjam dan update jumlah pada table buku
     */
    public boolean insertPinjam(int id, int anggotaId, String userId, String tanggal_pinjam, List<TransaksiDetailView> list) {
        String query = "INSERT INTO Transaksi (id, anggota_id, mdb, mdd) VALUES(" + id + "," + anggotaId + ",'" + userId + "',NOW())";
        String queryDetail = "INSERT INTO TransaksiDetail (transaksi_id,inventaris_id,tanggal_pinjam) VALUES (" + id + ",?,'" + tanggal_pinjam + "')";
        String queryUpdateBuku = "UPDATE Inventaris SET status = 1 WHERE id =?";

        PreparedStatement statement = null;
        PreparedStatement statementDetail = null;
        PreparedStatement statementBuku = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query);
            statementDetail = connection.prepareStatement(queryDetail);
            statementBuku = connection.prepareStatement(queryUpdateBuku);
            for (int i = 0; i < list.size(); i++) {
                statementDetail.setString(1, list.get(i).getIdInventaris());
                statementBuku.setString(1, list.get(i).getIdInventaris());

                statementDetail.addBatch();
                statementBuku.addBatch();
            }

            if (statement.executeUpdate() == 1
                    && statementDetail.executeBatch().length > 0
                    && statementBuku.executeBatch().length > 0) {
                connection.commit();
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when insert data \n Error: " + e.getMessage());
            try {
                connection.rollback();
                statement.close();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return true;
    }

    /*
     * Fungsi untuk update data peminjaman, table yang diupdate table pinjam, table detail pinjam dan update jumlah pada table buku
     * data detailpinjam yang diupdate hanya yang tanggalnya tidak null dan status not complete
     */
    public boolean updatePinjam(int pinjamId, int anggotaId, String userId, String tanggalKembali, List<TransaksiDetailView> list) {
        String query = "UPDATE transaksi SET  anggota_id=" + anggotaId + ", users='" + userId + "' WHERE id=" + pinjamId + "";
        String queryDetail = "UPDATE transaksiDetail SET tanggal_kembali='" + tanggalKembali + "', terlambat=?, denda=? WHERE id=?";
        String queryUpdateBuku = "UPDATE inventaris SET status = 0 WHERE id =?";
        //String queryDenda = "INSERT INTO Denda (transaksi_id,transaksidetail_id,tanggal,keterangan_id,bayar) VALUES (?,?,"+tanggalKembali+",1,"+bayar+")";
        PreparedStatement statement = null;
        PreparedStatement statementDetail = null;
        PreparedStatement statementBuku = null;
        PreparedStatement statementDenda = null;
//        System.out.println(tanggalKembali);
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query);
            //statement.setInt(1, anggotaId); statement.setString(2, userId); statement.setInt(3, pinjamId);
            statementDetail = connection.prepareStatement(queryDetail);
            statementBuku = connection.prepareStatement(queryUpdateBuku);
            for (int i = 0; i < list.size(); i++) {
                if (list.get(i).getTanggalKembali() != null && !isComplete(list.get(i).getId())) {
                    //statementDetail.setDate(1, (java.sql.Date) list.get(i).getTanggalKembali());
//                    statementDetail.setInt(1, list.get(i).getTerlambat());
//                    statementDetail.setDouble(2, list.get(i).getDenda());
                    statementDetail.setInt(1, list.get(i).getTerlambat());
                    statementDetail.setInt(2, list.get(i).getDenda());
                    statementDetail.setInt(3, list.get(i).getId());
                    statementDetail.addBatch();
                    statementBuku.setString(1, list.get(i).getIdInventaris());
                    statementBuku.addBatch();
                }
            }
            if (statement.executeUpdate() == 1
                    && statementDetail.executeBatch().length > 0) {
                statementBuku.executeBatch();
                connection.commit();
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when Update data \n Error: " + e.getMessage());
            try {
                connection.rollback();
                statement.close();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return true;
    }
    /*
     * Fungsi untuk memeriksa  status peminjaman compelete atau not compelet, return true jika complete
     */

    public boolean isComplete(int id) {
        boolean result = false;
        String status = null;
        try {
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery("SELECT status from vTransaksi WHERE detail_id=" + id);
            if (rs.next()) {
                status = rs.getString(1);
            }
            if (status.equals("Selesai")) {
                result = true;
            }
        } catch (SQLException e) {
        }
        return result;
    }

    public TransaksiView getTransaksi(int id) {
        String query = "SELECT a.id, a.anggota_id, b.nis_nik, b.nama, a.mdb, c.nama FROM transaksi a JOIN anggota b ON a.anggota_id = b.id JOIN users c ON a.mdb = c.id WHERE a.id =" + id;
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(query);//statement.setInt(1, id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {

                tv = new TransaksiView(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getInt(5), rs.getString(6));
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data \n Error: " + e.getMessage());
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return tv;
    }

    public List<TransaksiDetailView> getListDetailTransaksi(int id) {
        Date date;
        ListDetailTransaksi = new ArrayList<TransaksiDetailView>();
        String query = "SELECT a.id, a.transaksi_id, a.inventaris_id, b.judul, a.tanggal_pinjam, a.tanggal_kembali, a.terlambat, a.denda FROM transaksi_detail a JOIN vInventaris b ON a.inventaris_id = b.id_inventaris WHERE a.transaksi_id = " + id;
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(query); //statement.setInt(1, id);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                //if(rs.getDate(5).toString().equals("1900-01-01"))date=null; else date = rs.getDate(5);

                detailTransaksi = new TransaksiDetailView(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getDate(5), rs.getDate(6), rs.getInt(7), rs.getInt(8));
                ListDetailTransaksi.add(detailTransaksi);
                //System.out.println("Duuul  :  "+rs.getInt(2));
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data \n Error: " + e.getMessage());
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return ListDetailTransaksi;
    }

    public int ambilDendaBuku() {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("select DISTINCT * from denda_buku");
            //statement.setString(1, transaksi_id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception e) {
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return 0;
    }

    public Connection getConnection() {
        return connection;
    }

    public int jumlahDenda(String tanggala, String tanggalb) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("Select 'Total Denda'=SUM(denda) from vTransaksi where tanggal_pinjam BETWEEN '" + tanggala + "' AND '" + tanggalb + "'");
            //statement.setString(1, transaksi_id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception e) {
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return 0;

    }

    public String getJudul(String idInventaris) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("select judul from vInventaris where status='Tersedia' AND inventaris_id='" + idInventaris + "'");
            //statement.setString(1, transaksi_id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                return rs.getString(1);
            }
        } catch (Exception e) {
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return "";
    }

    public int cekHariLibur(String tglPinjam, String tglKembali) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("Select 'jumlah hari libur'=COUNT(*) from HariLibur where tanggal BETWEEN '" + tglPinjam + "' AND '" + tglKembali + "'");
            //statement.setString(1, transaksi_id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception e) {
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return 0;
    }

    public int cekTransaksiSementara(String inventaris_id) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("select count(*) from TransaksiSementara where id ='" + inventaris_id + "'");
            //statement.setString(1, transaksi_id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception e) {
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return 0;
    }

    public boolean insertPinjamSementara(String inventaris_id) {
        String query = "INSERT INTO TransaksiSementara VALUES('" + inventaris_id + "')";

        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query);
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when insert data \n Error: " + e.getMessage());
            try {
                connection.rollback();
                statement.close();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return true;
    }

    public boolean deletePinjamSementara(String kodeInventaris) {
        String query = "DELETE FROM TransaksiSementara where id='"+kodeInventaris+"'";

        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query);
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when insert data \n Error: " + e.getMessage());
            try {
                connection.rollback();
                statement.close();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return true;
    }
    public boolean deleteAllPinjamSementara() {
        String query = "DELETE FROM TransaksiSementara";

        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query);
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when insert data \n Error: " + e.getMessage());
            try {
                connection.rollback();
                statement.close();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return true;
    }
}
